<?php

namespace App\V3\V3_2_7\Models;

use DateTimeInterface;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class LogOutStorageModel extends Model
{


    use HasFactory;

    protected $table = 'log_outstorage';

    protected $dateFormat = 'U';//这个时间字段被为被格式化为UNIX 时间戳的形式存储

    //toArray 时间转换问题
    public static function outLogExport(array $data, array $user, bool $startTime, bool $endTime)
    {
        $query = LogOutstorageModel::select(
            'a.id',
            'a.type',
            'f.storage_name',
            'f.storage_address',
            'g.storage_name as next_storage_name',
            'g.storage_address as next_storage_address',
            //            'a.materiel_class_name',
            //            'a.materiel_class_no',
            'e.materiel_name',
            'e.type_name',
            'e.materiel_no',
            'e.materiel_img',
            'a.operate_num',
            'a.last_storage_num',
            'a.next_storage_num',
            'a.materiel_mean_cost',
            'a.remark',
            'a.is_cancel',
            'a.out_storage_time',
            'c.class_name',
            'c.class_no',
            'b.class_name as materiel_class_name',
            'b.class_no as materiel_class_no',
            'd.username',
            'd.nickname',
            'h.location_name',
            'i.shelf_name',
            'a.serial_number',
            'a.created_at',
            'j.batch_no',
            'a.allocate_no',
            'k.batch_no as before_allocate_batch_no',
            'm.shelf_name as next_shelf_name',
            'n.location_name as next_location_name'
        )
            ->from('log_outstorage as a')
            ->leftJoin('materiel_class as b', 'b.id', '=', 'a.materiel_class_id')
            ->leftJoin('materiel_class as c', 'b.parent_id', '=', 'c.id')
            ->leftJoin('admin as d', 'd.id', '=', 'a.user_id')
            ->leftJoin('materiel_type as e', 'e.id', '=', 'a.materiel_type_id')
            ->leftJoin('storage as f', 'f.id', '=', 'a.storage_id')
            ->leftJoin('storage as g', 'g.id', '=', 'a.next_storage_id')
            ->leftJoin('storage_shelf_location as h', 'h.id', '=', 'a.location_id')
            ->leftJoin('storage_shelf as i', 'i.id', '=', 'a.shelf_id')
            ->leftJoin('storage_batch as j', 'j.id', '=', 'a.storage_batch_id')
            ->leftJoin('storage_batch as k', 'k.id', '=', 'a.before_allocate_batch_id')
            ->leftJoin('storage_manage as l', 'l.id', '=', 'a.next_storage_manage_id')
            ->leftJoin('storage_shelf as m', 'm.id', '=', 'l.shelf_id')
            ->leftJoin('storage_shelf_location as n', 'n.id', '=', 'l.location_id')
            ->where('a.delete_flag', 0)
            ->where($user)
            ->where('a.is_cancel', $data['status']);

        if ($data['materiel_class_id'] != 0) {
            $query->where('a.materiel_class_id', $data['materiel_class_id']);
        }

        $keyword = empty($data['keyword']) ? '' : $data['keyword'];

        if (!empty($startTime) && !empty($endTime)) {
            $query->whereBetween('in_storage_time', [$startTime, $endTime]);
        } else if (!empty($startTime) && empty($endTime)) {
            $query->where('in_storage_time', '>=', $startTime);
        } else if (empty($startTime) && !empty($endTime)) {
            $query->where('in_storage_time', '<=', $endTime);
        }

        return $query->where(function ($query) use ($keyword, $data) {
            $query->orWhere('b.class_name', 'like', '%' . $keyword . '%');
            $query->orWhere('b.class_no', 'like', '%' . $keyword . '%');
            $query->orWhere('c.class_name', 'like', '%' . $keyword . '%');
            $query->orWhere('c.class_no', 'like', '%' . $keyword . '%');
            $query->orWhere('e.materiel_name', 'like', '%' . $keyword . '%');
            $query->orWhere('e.type_name', 'like', '%' . $keyword . '%');
            $query->orWhere('e.materiel_no', 'like', '%' . $keyword . '%');
            $query->orWhere('f.storage_name', 'like', '%' . $keyword . '%');
            $query->orWhere('g.storage_name', 'like', '%' . $keyword . '%');

            if ($data['type'] == 0) {
                $query->orWhere('purchase_no', 'like', '%' . $keyword . '%');
            }
        })
            ->orderBy('a.id', 'desc')
            ->get()->toArray();
    }

    protected function serializeDate(DateTimeInterface $date)
    {
        return $date->format('Y-m-d H:i:s');
    }

    public static function getCount($condition, $date)
    {
        $res =  self::query()
            ->where([
                'delete_flag' => 0,
                'is_cancel' => 0,
                'type'=> 2,
            ])->where($condition)
            ->whereBetween('created_at', [$date[0], $date[1]])
            ->groupBy('created_at')->get()->toArray();
       return count($res);
    }

    /**
     * $dates 数组内的列转换为日期格式
     *
     * @var array
     */
    protected $dates = [
        'out_storage_time',
    ];

    //今日出库总数
    static public function getTodayOutCount($condition, $startTime, $endTime)
    {
        return self::where($condition)
            ->where('type', '!=', 1)
            ->where('is_cancel', 0)
            ->whereBetween('created_at', [$startTime, $endTime])
            ->sum('operate_num');
    }


    static public function getOutCount($condition, $startTime, $endTime, $type, $flag)
    {
        return self::query()->select(
            DB::raw('SUM(operate_num) AS total_num'),
            DB::raw('SUM(operate_num*sell_price) AS total_price'),
            DB::raw('SUM(operate_num*materiel_mean_cost) AS total_cost'),
        )->where(function ($query) use ($type, $flag, $condition) {
            if ($flag === 1) {
                $query->where('type', $type);
            } else {
                $query->where('all_id_type', $type);
            }
            $query->where($condition);
            $query->where('is_cancel', 0);
        })->whereBetween('created_at', [$startTime, $endTime])->first();
    }

    //今日出库总数
    static public function getTodayAllocateCount($condition, $startTime, $endTime)
    {
        return self::where($condition)
            ->where('type', '=', 1)
            ->where('is_cancel', 0)
            ->whereBetween('created_at', [$startTime, $endTime])
            ->sum('operate_num');
    }

    //今日销售金额和总利润
    static public function getTodaySellMoney($condition, $startTime, $endTime)
    {
        //总成本
        $priceInfo = LogOutStorageModel::select(
            DB::raw('SUM(operate_num * materiel_mean_cost) as total_price')
        )
            ->from('log_outstorage as a')
            ->where('is_cancel', 0)
            ->where('type', '!=', 1)
            ->where($condition)
            ->whereBetween('created_at', [$startTime, $endTime])
            ->first();

        $result['total_cost'] = empty($priceInfo) ? 0 : sprintf('%.6f', $priceInfo['total_price']);

        //总售价
        $sellPriceInfo = LogOutStorageModel::select(
            DB::raw('SUM(operate_num * sell_price) as total_sell_price')
        )
            ->from('log_outstorage as a')
            ->where('is_cancel', 0)
            ->where('type', '!=', 1)
            ->where($condition)
            ->whereBetween('created_at', [$startTime, $endTime])
            ->first();
        $result['total_sell_price'] = empty($sellPriceInfo) ? 0 : sprintf('%.6f', $sellPriceInfo['total_sell_price']);

        //总利润
        $result['total_income'] = sprintf('%.6f', $result['total_sell_price'] - $result['total_cost']);
        return $result;
    }

    //销售单利润
    static public function getSellMoney($condition, $startTime, $endTime)
    {
        //总成本
        $priceInfo = LogOutStorageModel::select(
            DB::raw('SUM(operate_num * materiel_mean_cost) as total_cost'),
            DB::raw('SUM(operate_num * sell_price) as total_price')
        )->from('log_outstorage as a')
            ->where('is_cancel', 0)
            ->where('type', 1)
            ->where($condition)
            ->whereBetween('created_at', [$startTime, $endTime])
            ->first();
        $result['total_cost'] = empty($priceInfo) ? 0 : sprintf('%.6f', $priceInfo['total_cost']);
        $result['total_price'] = empty($priceInfo) ? 0 : sprintf('%.6f', $priceInfo['total_price']);
        //总利润
        $result['total_income'] = sprintf('%.6f', $result['total_price'] - $result['total_cost']);
        return $result;
    }

    public static function logOutShelfInfo($typeId, $storageId)
    {
        return LogOutStorageModel::query()->select(
            'a.shelf_id',
            'shelf_name',
            'a.location_id',
            'location_name',
            'd.materiel_name',
            'd.type_name',
            DB::raw('SUM(operate_num) as operate_num')
        )
            ->from('log_outstorage as a')
            ->leftJoin('storage_shelf as b', 'a.shelf_id', '=', 'b.id')
            ->leftJoin('storage_shelf_location as c', 'a.location_id', '=', 'c.id')
            ->leftJoin('materiel_type as d', 'd.id', '=', 'a.materiel_type_id')
            ->where([
                'materiel_type_id' => $typeId,
                'a.storage_id' => $storageId,
            ])
            ->where('a.is_cancel', 0)
            ->groupBy('location_id')
            ->get()->toArray();
    }

    public static function getOutStorageMoney($condition, $startTime, $endTime, $type = 2)
    {
        return self::query()
            ->select(
                DB::raw("SUM(sell_price) as out_total_sell_price"),
                DB::raw("SUM(materiel_mean_cost) as out_total_cost"),
                DB::raw("SUM(operate_num) as out_total_num")
            )->where($condition)
            ->where([
                'type' => $type,
                'is_cancel' => 0,
                'delete_flag' => 0,
            ])->whereBetween('created_at', [$startTime, $endTime])
            ->first();

    }
}
